<?php
/*
  $Id: stats_products_purchased.php, v1.29 2003/06/29 22:50:52 hpdl Exp $

  osCommerce, Open Source E-Commerce Solutions
  http://www.oscommerce.com

  Copyright (c) 2003 osCommerce

  Released under the GNU General Public License */

  require('includes/application_top.php');

  require(DIR_WS_CLASSES . 'currencies.php');
  $currencies = new currencies();

  if ($_GET['month'] == '') {
    $month = date('m');
    $year = '20' . date('y');
  } else {
    $month = $_GET['month'];
    $year = $_GET['year'];
  }

  $months = array();
  $months[] = array('id' => 1, 'text' => 'January');
  $months[] = array('id' => 2, 'text' => 'February');
  $months[] = array('id' => 3, 'text' => 'March');
  $months[] = array('id' => 4, 'text' => 'April');
  $months[] = array('id' => 5, 'text' => 'May');
  $months[] = array('id' => 6, 'text' => 'June');
  $months[] = array('id' => 7, 'text' => 'July');
  $months[] = array('id' => 8, 'text' => 'August');
  $months[] = array('id' => 9, 'text' => 'September');
  $months[] = array('id' => 10, 'text' => 'October');
  $months[] = array('id' => 11, 'text' => 'November');
  $months[] = array('id' => 12, 'text' => 'December');

  $years = array();

  $years[] = array('id' => 2005, 'text' => '2005');
  $years[] = array('id' => 2006, 'text' => '2006');
  $years[] = array('id' => 2007, 'text' => '2007');
  $years[] = array('id' => 2008, 'text' => '2008');
  $years[] = array('id' => 2009, 'text' => '2009');
  $years[] = array('id' => 2010, 'text' => '2010');
  $years[] = array('id' => 2011, 'text' => '2011');
  $years[] = array('id' => 2012, 'text' => '2012');

// bobf 9/20/07
// We want the default to be "shipped", not "all orders"
  if(isset($_GET['status']))
  {
    $status = (int)$_GET['status'];
  }
  else
  {
    $status = 0xDEADBEEF;
  }


// bobf 9/20/07 sorted by name rather than id
  $statuses_query = tep_db_query("select * from orders_status where language_id = '" . (int)$languages_id . "' order by orders_status_name");
  $statuses = array();
  $statuses[] = array('id' => 0, 'text' => TEXT_ORDERS_STATUS);
  while ($st = tep_db_fetch_array($statuses_query)) {
     $statuses[] = array('id' => $st['orders_status_id'], 'text' => $st['orders_status_name']);
// bobf 9/20/07
// This isn't the best way to do this, but it works.  We want
     if($status == 0xDEADBEEF && $st['orders_status_name'] == "Shipped")
     {
       $status = $st['orders_status_id'];
     }
  }

  if ($status != 0)  {
    $os = " and o.orders_status = " . $status . " ";
  } else {
    $os = '';
  }

  if (isset($_GET['keywords']) && $_GET['keywords'] != '') {
  	$keywords = trim($_GET['keywords']);
  }
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="<?php if(!$print) {
  echo 'includes/stylesheet.css';}
  else echo 'includes/printer.css'; ?>">
</head>
<body bgcolor="#FFFFFF">
<?php
// set printer-friendly toggle
(tep_db_prepare_input($_GET['print']=='yes')) ? $print=true : $print=false;
// set inversion toggle
(tep_db_prepare_input($_GET['invert']=='yes')) ? $invert=true : $invert=false;
?>
<!-- header //-->
<?php if(!$print) require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
  <tr>

<?php  // suppress left column for printer-friendly version
  if(!$print) {?>
  <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
  <!-- left_navigation //-->
  <?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
  <!-- left_navigation_eof //-->
        </table></td>
<?php };  ?>

<!-- body_text //-->
    <td width="100%" valign="top">
  <table border="0" width="100%" cellspacing="0" cellpadding="2">
      <tr>
        <td>
    <table border="0" width="100%" cellspacing="0" cellpadding="0">
<?php if ($print) {
  echo "<tr><td class=\"pageHeading\">" . STORE_NAME ."</td></tr>";
  };
?>
      <tr>
        <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
            <td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
            <td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
          </tr>
        </table></td>
      </tr>

	  <tr>
	    <td>
		  <table border="0" align="left" width="100%" cellspacing="0" cellpadding="2">
		  <?php echo tep_draw_form('date_range', FILENAME_STATS_PRODUCTS_PURCHASED, '', 'get');
		          if (isset($_GET[tep_session_name()])) {
		            echo tep_draw_hidden_field(tep_session_name(), $_GET[tep_session_name()]);
        }
        ?>
		    <tr>
			  <td class="smallText">

<?php
	$manufacturers_query = tep_db_query("select manufacturers_id, manufacturers_name from " . TABLE_MANUFACTURERS . " order by manufacturers_name");
     $manufacturers_array = array();
	 $manufacturers_array[] = array('id' => '0', 'text' => TEXT_SELECT_MANUFACTURER);
      while ($manufacturers = tep_db_fetch_array($manufacturers_query)) {
        $manufacturers_name = $manufacturers['manufacturers_name'];
        $manufacturers_array[] = array('id' => $manufacturers['manufacturers_id'],
                                       'text' => $manufacturers_name);
	}

	echo ENTRY_KEYWORDS . '&nbsp;&nbsp;' . tep_draw_input_field('keywords', $keywords, 'size="20"') . '<br><br>';

	echo ENTRY_YEAR . '&nbsp;&nbsp;' . tep_draw_pull_down_menu('year', $years, $year, 'onchange=\'this.form.submit();\'') . '&nbsp;&nbsp;&nbsp;&nbsp;';

	echo ENTRY_MONTH . '&nbsp;&nbsp;' . tep_draw_pull_down_menu('month', $months, $month, 'onchange=\'this.form.submit();\'') . '&nbsp;&nbsp;&nbsp;&nbsp;';

	echo ENTRY_STATUS . '&nbsp;&nbsp;' . tep_draw_pull_down_menu('status', $statuses, $status, 'onchange=\'this.form.submit();\'') . '&nbsp;&nbsp;&nbsp;&nbsp;';

	echo ENTRY_MANUFACTURER . '&nbsp;&nbsp;' . tep_draw_pull_down_menu('manufacturers_id', $manufacturers_array, (isset($_GET['manufacturers_id']) ? $_GET['manufacturers_id'] : ''), 'onChange="this.form.submit();" size="1"') . '&nbsp;&nbsp;&nbsp;';

    echo '&nbsp;' . ENTRY_PRINTABLE . tep_draw_checkbox_field('printable', $print) . '&nbsp;&nbsp;&nbsp;';
    echo ENTRY_SORT_BY_GROSS . tep_draw_checkbox_field('gross', $gross);

    echo '</td></form>';

    $totalgross = 0;
	$totalquantity = 0;
?>

		  </tr>
		</table></td>
	  </tr>

	  <tr>
        <td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>
      </tr>

      <tr>
        <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
            <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr class="dataTableHeadingRow">
                <td class="dataTableHeadingContent">&nbsp;<?php echo TABLE_HEADING_NUMBER; ?></td>
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_MANUFACTURERS; ?></td>
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PRODUCTS; ?></td>
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_MODEL; ?></td>
                <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_PURCHASED; ?>&nbsp;</td>
                <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_GROSS; ?>&nbsp;&nbsp;&nbsp;&nbsp;</td>
          </tr>
<?php
  if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] > 0) {
  if ($gross == 'on') {
     $products_query_raw = "select op.products_id, m.manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum, sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE month(o.date_purchased) = " . $month . " and year(o.date_purchased) = " . $year . $os . " AND o.orders_id = op.orders_id and op.products_id = p.products_id and p.manufacturers_id = m.manufacturers_id and p.manufacturers_id = " . $_GET['manufacturers_id'] . " " . (isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" . $keywords . "%' OR m.manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY gross DESC, quantitysum DESC, op.products_model";
   } else {
     $products_query_raw = "select op.products_id, m.manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum, sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE month(o.date_purchased) = " . $month . " and year(o.date_purchased) = " . $year . $os . " AND o.orders_id = op.orders_id and op.products_id = p.products_id and p.manufacturers_id = m.manufacturers_id and p.manufacturers_id = " . $_GET['manufacturers_id'] . " " . (isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" . $keywords . "%' OR m.manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY quantitysum DESC, op.products_model";
   }
 } else {
 if ($gross == 'on') {
     $products_query_raw = "select op.products_id, m.manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum, sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE month(o.date_purchased) = " . $month . " and year(o.date_purchased) = " . $year . $os . " AND o.orders_id = op.orders_id and op.products_id = p.products_id and p.manufacturers_id = m.manufacturers_id " . (isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" . $keywords . "%' OR m.manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY gross DESC, quantitysum DESC, op.products_model";
   } else {
     $products_query_raw = "select op.products_id, m.manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum, sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE month(o.date_purchased) = " . $month . " and year(o.date_purchased) = " . $year . $os . " AND o.orders_id = op.orders_id and op.products_id = p.products_id and p.manufacturers_id = m.manufacturers_id " . (isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" . $keywords . "%' OR m.manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY quantitysum DESC, op.products_model";
   }
 }
  $rows = 0;
  $products_query = tep_db_query($products_query_raw);

  while ($products = tep_db_fetch_array($products_query)) {
    $rows ++;

    $totalgross = $totalgross + $products['gross'];
    $totalquantity = $totalquantity + $products['quantitysum'];

    if (strlen($rows) < 2) {
     $rows = '0' . $rows;
    }
?>
              <tr bgcolor="<?php echo ((++$cnt)%2 == 0) ? '#E0E0E0' : '#FFFFFF' ?>" id="defaultSelected" class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)">
              <td class="dataTableContent">&nbsp;<?php echo $rows; ?>.</td>
			  <td class="dataTableContent"><?php echo $products['manufacturers_name']; ?>
              <td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CATEGORIES, 'action=new_product_preview&read=only&pID=' . $products['products_id'] . '&origin=' . FILENAME_STATS_PRODUCTS_PURCHASED . '?page=' . (int)$_GET['page']) . '">' . $products['products_name'] . '</a>'; ?></td>
              <td class="dataTableContent"><?php echo $products['products_model']; ?>
              <td class="dataTableContent" align="center"><?php echo $products['quantitysum']; ?>&nbsp;</td>
              <td class="dataTableContent" align="right"><?php echo sprintf('%01.2f', $products['gross']); ?>&nbsp;</td>
            </tr>
<?php
   }
?>

			<tr>
			  <td><?php echo tep_draw_separator('pixel_trans.gif', '1', '5'); ?></td>
			</tr>

            <tr>
			  <td class="dataTableContent"></td>
			  <td class="dataTableContent"></td>
			  <td class="dataTableContent"></td>
			  <td class="dataTableContent"></td>
			  <td class="dataTableContent" align="left"><b><?php echo ENTRY_TOTAL . ' ' . $totalquantity; ?></b>&nbsp;&nbsp;</td>
			  <td class="dataTableContent" align="right"><b>Total:&nbsp;&nbsp;$<?php echo sprintf('%01.2f', $totalgross); ?></b>&nbsp;</td>

			  </tr>
            </table></td>
          </tr>
          <tr>
            <td colspan="3"><table border="0" width="100%" cellspacing="0" cellpadding="2">

            </table></td>
          </tr>
        </table></td>
      </tr>
    </table></td>
<!-- body_text_eof //-->
  </tr>
</table>
<!-- footer //-->
<?php  // suppress footer for printer-friendly version
  if(!$print) require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>
